import pytz
import requests
import time
import base64
import random
import json
import pymysql
from datetime import datetime
from apscheduler.schedulers.blocking import BlockingScheduler

# 连接MySQL数据库
# 注意：这可能需要一些特定的MySQL服务器设置，如端口和SSL配置等。
# 你将需要以字符串形式指定这些选项。
conn = pymysql.connect(host='localhost',
                       user='root',
                       password='123456',
                       database='cats',
                       port=3306,
                       charset='utf8mb4',
                       cursorclass=pymysql.cursors.DictCursor)





def insert_data(data_to_insert):
    # print(data_to_insert)
    # data_list = json.dumps(data_list)
    try:
        # 创建一个游标
        with conn.cursor() as cursor:
            # 创建一个或多个需要插入的数据的实例
            # data_to_insert = [(item['holderAddress'], item['rate'], item['value'], item['value']) for item in data_list]
            # data_to_insert = (
            #     ('address', 'rate', ...),
            #     ('column1_value2', 'column2_value2', ...),
            #     # 更多数据，如果有的话...
            # )

            # 如果你需要插入的数据是字典格式，可以使用以下SQL语句
            sql = "INSERT INTO address_list (address, rate,hold, time) VALUES (%s, %s,%s, %s)"
            # cursor.execute(sql)
            # 使用游标执行SQL语句，将数据插入到数据库
            for item in data_to_insert:
                # print(item)
                cursor.execute(sql, item)

            # 提交事务，以便数据被持久化到数据库
            conn.commit()
            print('数据插入成功。')
            query = "select hold, address,  GROUP_CONCAT(CONCAT(time,'_',hold)SEPARATOR ',') as record from (select address,hold,time from address_list where time > CURDATE() - INTERVAL 7 DAY order by hold desc) as a group by address  order by hold desc"
            # query = "select hold, address,  GROUP_CONCAT(hold)SEPARATOR ',') as record from (select address,hold,time from address_list where time > CURDATE() - INTERVAL 7 DAY order by hold desc) as a group by address  order by hold desc"
            cursor.execute(query)
            rows = cursor.fetchall()
            # print(rows)

    except pymysql.Error as error:
        # 如果发生错误，回滚事务并打印错误消息
        print("插入数据错误: {}".format(error))
        conn.rollback()

    # finally:
        # 无论是否发生异常，都要确保关闭数据库连接
        # conn.close()


s = requests.session()

API_KEY = "a2c903cc-b31e-4547-9299-b6d07b7631ab"

e = list(API_KEY)
# print(e)
n = e[:8]
# print(n)
key_list = e + n
result = ''.join(key_list)
# t = e.concat(n).join("")
# print(result)

offset = 0


def get_api_key(time_stamp):

    key = '-b31e-4547-9299-b6d07b7631aba2c903cc'
    now = int(round(time_stamp * 1000)) + 1111111111111
    a = str(random.randint(1, 10))
    b = str(random.randint(1, 10))
    c = str(random.randint(1, 10))
    # now = 1720578528015 + 1111111111111
    time_str = str(now) + a + b + c
    # print(a)
    # print(b)
    # print(c)
    # print(time_str)
    all_key_str = key + '|' + time_str
    key_encode = base64.b64encode(all_key_str.encode('utf-8'))
    # t_encode = base64.b64encode(time_str.encode('utf-8'))
    # print(str(key_encode,'utf-8'))
    # print(str(t_encode,'utf-8'))
    api_key = str(key_encode,'utf-8')
    return api_key

# 获取当前时间戳
def get_time():
    time_stamp = time.time()  # 时间戳获取
    return int(round(time_stamp * 1000))


# 持仓分布
def get_data():
    time_stamp = time.time()  # 时间戳获取
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0.0.0 Safari/537.36 Edg/126.0.0.0',
        'X-Apikey': get_api_key(time_stamp)
    }
    # print(api_key)
    url = 'https://www.oklink.com/api/explorer/v2/polygon/tokens/holders/0x0030f47d6a73bc518cf18fe027ea91dd6b2b6003' \
          '?offset={}&limit=100&sort=value%2Cdesc&t={}'.format(offset, get_time() )
    res = s.get(url, headers=headers).text
    try:
        res = json.loads(res)
        # print(res['data']['hits'])
        records = res['data']['hits']
        data_to_insert = []
        for index, item in enumerate(records):
            # print('排名第{}钱包地址:{}，持仓{}张'.format(item['rank'], item['holderAddress'], int(item['value'])))
            current_time = datetime.now()

            # 格式化时间为字符串，例如 'YYYY-MM-DD HH:MM:SS'
            formatted_time = current_time.strftime('%Y-%m-%d %H:%M:%S')
            data_to_insert.append((item['holderAddress'], item['rate'], item['value'], formatted_time))
        insert_data(data_to_insert)
        # print(data_to_insert)
    except ValueError:
        print('输入的值不是合法的整数')
    except Exception as r:
        print(r)
        print('未知错误 %s' % (r))
    # 没有预先判断到的错误怎么办?
    # ZeroDivisionError
    # finally:
        # 无论是否有异常，都会执行的代码
        # print('%%%%%%%%%%%%%%%')
    # ValueError


# 转账列表
def transfer_detail_5min():
    time_stamp = time.time()  # 时间戳获取
    start = int(round((time_stamp - 3600) * 1000))
    end = int(round(time_stamp * 1000))
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0.0.0 Safari/537.36 Edg/126.0.0.0',
        'X-Apikey': get_api_key(time_stamp)
    }
    url = 'https://www.oklink.com/api/explorer/v2/polygon/transfers?offset=0&tokenAddress=0x0030f47d6a73bc518cf18fe027ea91dd6b2b6003&direction=3&otherAddress=&start={}&end={}&limit=1000&t={}'.format(start, end, end)
    res = s.get(url, headers=headers).text
    print(start)
    print(end)
    try:
        response = json.loads(res)
        print(response['data']['hits'])
        hits_list = response['data']['hits']
    except json.JSONDecodeError:
        print('Invalid JSON format')
    except KeyError:
        print('Invalid key')


if __name__ == '__main__':
    get_data()
    # # 创建调度器
    scheduler = BlockingScheduler(timezone=pytz.timezone("Asia/Shanghai"))
    #
    # # 添加定时任务
    scheduler.add_job(get_data, 'interval', minutes=10)  # 每隔10分钟执行一次
    #
    # # 启动调度器
    scheduler.start()
    # transfer_detail_5min()

